package com.demo;

import com.demo.beans.Emp;
import com.demo.beans.User;
import com.demo.utils.JDBCUtils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Scanner;

/**
 * 设计模式  开闭原则
 */
public class Test {
    public static void main(String[] args) {
        login();
    }

    private static void login(){
        try {
            //        1.从键盘读数据
            Scanner scanner = new Scanner(System.in);
            String srcData = scanner.nextLine();
            //        user psd psd
            String[] split = srcData.split(" ",2);
            String name = split[0];
            String psd = split[1];
            //        2.链接数据库 操作数据库
//            int resultInt = handleDB(name, psd);
            User user = new User();
            user.setUser(name);
            user.setPsd(psd);
            List<User> users = handleDB7();
            //        3.判断数据库操作的结果，提示用户
            for (User item : users) {
                System.out.println(item +"\n");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void registerUser(){
        try {
            //        1.从键盘读数据
            Scanner scanner = new Scanner(System.in);
            String srcData = scanner.nextLine();
            //        user psd psd
            String[] split = srcData.split(" ",3);
            if (split.length<3){
                System.out.println("您的输入不符合规则，请重新输入");
                return;
            }
            String name = split[0];
            String psd = split[1];
            String psd2 = split[2];
            if (!psd.equals(psd2)){
                System.out.println("您两次输入的密码不同，请重新输入");
                return;
            }
            //        2.链接数据库 操作数据库
//            int resultInt = handleDB(name, psd);
            User user = new User();
            user.setUser(name);
            user.setPsd(psd);
            user.setEmail(psd+"@");
            int resultInt = handleDB4(user);
            //        3.判断数据库操作的结果，提示用户
            resultToUser(resultInt);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 操作数据库 将用户名 密码插入到数据库表中，再返回数据库操作的结果
     */
    private static int handleDB(String name, String psd) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "insert into user(USER,PSD) values (?,?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1,name);
        statement.setString(2,psd);
        return statement.executeUpdate();
    }

    /**
     * 操作数据库 将用户名 密码插入到数据库表中，再返回数据库操作的结果
     */
    private static int handleDB(User user) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        String sql = "insert into user(USER,PSD) values (?,?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setString(1,user.getUser());
        statement.setString(2,user.getPsd());
        return statement.executeUpdate();
    }

    /**
     * 操作数据库 将用户名 密码插入到数据库表中，再返回数据库操作的结果
     */
    private static int handleDB2(User user) throws SQLException, IllegalAccessException {
        Connection conn = JDBCUtils.getConn();
        String sqlPre = "insert into user(";
        String sqlSuf = ") values ";
        // 反射  获取所有的属性
        Field[] fields = User.class.getDeclaredFields();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder("(");
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            String fileName = fields[i].getName();
            sb.append(fileName);
            sb2.append("?");
            if (i != fields.length -1){
                sb.append(",");
                sb2.append(",");
            }else {
                sb2.append(")");
            }
        }
        String sql = sqlPre + sb.toString() + sqlSuf +sb2;
        System.out.println("执行的sql语句是："+ sql);
        PreparedStatement statement = conn.prepareStatement(sql);
//        获取公共的属性
//        Field[] fields = User.class.getFields();
        System.out.println("获取到的属性"+ Arrays.toString(fields));
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            statement.setString(i+1, (String) fields[i].get(user));
        }
        return statement.executeUpdate();
    }

    /**
     * 操作数据库 将用户名 密码插入到数据库表中，再返回数据库操作的结果
     */
    private static int handleDB3(User user) throws SQLException {
        String sql = "insert into user(USER,PSD) values(?,?)";
        return handleDBUpdate(sql, user.getUser(), user.getPsd());
    }

    private static int handleDB4(User user) throws SQLException {
        String sql = "insert into user(USER,PSD,EMAIL) values(?,?,?)";
        return handleDBUpdate(sql, user.getUser(), user.getPsd(), user.getEmail());
    }

    /**
     * 删除用户
     * @return 删除是否成功
     */
    private static int handleDB5(User user) throws SQLException {
        String sql = "delete from user where user=?";
        return handleDBUpdate(sql, user.getUser());
    }

    /**
     *
     * @param sql sql语句
     * @param params 替换sql语句中占位符的值
     * @return 执行的影响的行数
     */
    private static int handleDBUpdate(String sql, String... params) throws SQLException {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        return statement.executeUpdate();
    }

    /**
     * 登录
     * @param user
     */
    private static List<User> handleDB6(User user) throws Exception {
        String sql = "select user from user where user=? and psd=?";
        return handleDBQuery(sql, user.getUser(), user.getPsd());
    }

    /**
     * 查询所有的用户， 获取用户用户名
     */
    private static List<User> handleDB7() throws Exception {
        String sql = "select user,email from user";
        return handleDBQuery(sql);
    }

    private static List<User> handleDBQuery(String sql, String... params) throws Exception {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        ArrayList<User> userList = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
//          动态获取resultSet中的数据，兼容多列或一列的情况
            User userBean = new User();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                String columnName = metaData.getColumnLabel(i + 1);
                // 根据列名去除一行中对应的列的数据
                String itemData = resultSet.getString(columnName);
                // 将取出数据存放到 bean 中 例如：user  setUser(); email setEmail();  psd  setPsd();
                String methodName = "set"+ columnName.substring(0,1).toUpperCase() + columnName.substring(1);
                Method method = User.class.getDeclaredMethod(methodName, String.class);
                method.invoke(userBean, itemData);
            }
            userList.add(userBean);
        }
        return userList;
    }

    @org.junit.Test
    public void handleDB8() throws Exception {
        String sql = "select name,age,job,salary,entrydate from emp";
        List<Emp> users = handleDBQuery2(sql, Emp.class);
        for (Emp item : users) {
            System.out.println(item +"\n");
        }
    }

    public static <T> List<T> handleDBQuery2(String sql, Class<T> clazz, String... params) throws Exception {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        ArrayList<T> userList = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
//          动态获取resultSet中的数据，兼容多列或一列的情况
            T instance = clazz.getConstructor().newInstance();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                String columnName = metaData.getColumnLabel(i + 1);
                // 根据列名去除一行中对应的列的数据
                String itemData = resultSet.getString(columnName);
                // 将取出数据存放到 bean 中 例如：user  setUser(); email setEmail();  psd  setPsd();
                String methodName = "set"+ columnName.substring(0,1).toUpperCase() + columnName.substring(1);
                Method method = clazz.getDeclaredMethod(methodName, String.class);
                method.invoke(instance, itemData);
            }
            userList.add(instance);
        }
        return userList;
    }

    public static <T> List<T> handleDBQuery3(String sql, Class<T> clazz, String... params) throws Exception {
        Connection conn = JDBCUtils.getConn();
        PreparedStatement statement = conn.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            statement.setString(i + 1, params[i]);
        }
        ArrayList<T> userList = new ArrayList<>();
        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()){
//          动态获取resultSet中的数据，兼容多列或一列的情况
            T instance = clazz.getConstructor().newInstance();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                String columnName = metaData.getColumnLabel(i + 1);
                // 根据列名去除一行中对应的列的数据
                String itemData = resultSet.getString(columnName);
                // 将取出数据存放到 bean 中    内省
                BeanInfo beanInfo = Introspector.getBeanInfo(clazz, Object.class);
                PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
                PropertyDescriptor itemEnd = null;
                for (PropertyDescriptor item : propertyDescriptors) {
                    if(item.getName().equals(columnName)){
                        itemEnd = item;
                        break;
                    }
                }
                Method method = itemEnd.getWriteMethod();
                method.invoke(instance, itemData);
            }
            userList.add(instance);
        }
        return userList;
    }

    @org.junit.Test
    public void handleDB9() throws Exception {
        String sql = "select name,age,job,salary,entrydate from emp";
        List<Emp> users = handleDBQuery3(sql, Emp.class);
        for (Emp item : users) {
            System.out.println(item +"\n");
        }
    }

    @org.junit.Test
    public void test() throws Exception {
        BeanInfo beanInfo = Introspector.getBeanInfo(Emp.class, Object.class);
        PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
        for (PropertyDescriptor item:propertyDescriptors) {
            System.out.println("--->"+item.getName()+"===\t"+item.getReadMethod()+"===\t"+item.getWriteMethod());
        }
    }

    private static void resultToUser(int resultInt){
        if(resultInt>0){
            System.out.println("注册成功");
        }else {
            System.out.println("注册失败");
        }
    }

}
